﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Windows.Forms;
using QuanLyBanHang.DataConnection;

namespace QuanLyBanHang.QuerySQL
{
    class ClassDondathang
    {
        Connect conn = new Connect();
        //
        public DataTable ShowDH()
        {
            DataTable tb = new DataTable(); ;
            try
            {
                string sql = "select MADH,HOTEN,TENNV,NGAYDATHANG,NGAYGIAOHANG,TONGGIA_DH from DATHANG,KHACHHANG,NHANVIEN where DATHANG.MAKH = KHACHHANG.MAKH and DATHANG.MANV = NHANVIEN.MANV";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        //show Mã khác hàng
        public DataTable ShowMAKH()
        {
            DataTable tb = new DataTable();
            try
            {
                string sql = "select MAKH from KHACHHANG";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        //
        public DataTable ShowMADH(string madh, string makh)
        {
            DataTable tb = new DataTable();
            try
            {
                string sql = "select MADH,HOTEN,TENNV,NGAYDATHANG,NGAYGIAOHANG,TONGGIA_DH from DATHANG,KHACHHANG,NHANVIEN where MADH = '" + madh + "' and DATHANG.MAKH = '" + makh + "' and DATHANG.MAKH = KHACHHANG.MAKH and DATHANG.MANV = NHANVIEN.MANV ";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        // show mã sản phẩm
        public DataTable ShowMASP()
        {
            DataTable tb = new DataTable();
            try
            {
                string sql = "select MASP from SANPHAM";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        //
        public DataTable ShowCTPDH()
        {
            DataTable tb = new DataTable(); ;
            try
            {
                string sql = "select *from CHITIET_PDH";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        // show tên sp và giá
        public DataTable Show_CTSP(string masp)
        {
            DataTable tb = new DataTable();
            try
            {
                string sql = "select TENSP, GIABAN from SANPHAM where MASP = '" + masp + "' ";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        //thêm vào bảng đặt hàng
        public void ThemDH(string madh, string makh, string manv, string nd, string ng)
        {
            DateTime dt1 = Convert.ToDateTime(nd);
            DateTime dt2 = Convert.ToDateTime(ng);
            try
            {
                string sql = "insert into DATHANG(MADH,MAKH,MANV,NGAYDATHANG,NGAYGIAOHANG) values('" + madh + "','" + makh + "','" + manv + "','" + string.Format("{0:MM/dd/yyyy}", dt1) + "','" + string.Format("{0:MM/dd/yyyy}", dt2) + "')";
                conn.ExcuteQuerySql(sql);
                MessageBox.Show("Nhập chi tiết đơn đặt hàng !!!");
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
        //thêm chi tiết đặt hàng
        public void Them_CTPDH(string madh, string masp, string tensp, Double gia, Double slg, Double tong)
        {
            try
            {
                string sql = "insert into CHITIET_PDH values('" + madh + "','" + masp + "',N'" + tensp + "'," + gia + "," + slg + "," + tong + ")";
                conn.ExcuteQuerySql(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
        // cập nhập giá đơn đặt hàng
        public void SuaDH(string maDH, double tong)
        {
            try
            {
                string sql = "update DATHANG set TONGGIA_DH = " + tong + " where MADH = '" + maDH + "'";
                conn.ExcuteQuerySql(sql);
                MessageBox.Show("Thêm thành công !!!");
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }

        //tìm kiếm
        public DataTable TKDH(int i, string madh = null, string tenkh = null)
        {
            DataTable dt = new DataTable();
            switch (i)
            {
                case 0:
                    {
                        try
                        {
                            String sql = "select MADH,HOTEN,TENNV,NGAYDATHANG,NGAYGIAOHANG,TONGGIA_DH from DATHANG,KHACHHANG,NHANVIEN where MADH like '%" + madh + "%' and DATHANG.MAKH = KHACHHANG.MAKH and DATHANG.MANV = NHANVIEN.MANV";
                            dt = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 1:
                    {
                        try
                        {
                            String sql = "select MADH,HOTEN,TENNV,NGAYDATHANG,NGAYGIAOHANG,TONGGIA_DH from DATHANG,KHACHHANG,NHANVIEN where HOTEN like N'%" + tenkh + "%' and DATHANG.MAKH = KHACHHANG.MAKH and DATHANG.MANV = NHANVIEN.MANV";
                            dt = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
            }
            return dt;
        }
        // Xóa
        public void XoaDH(string madh)
        {
            try
            {
                string sql = "delete DATHANG where MADH = '" + madh + "'";
                conn.ExcuteQuerySql(sql);
                MessageBox.Show("Xóa thành công !!!");
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
        public void XoaCTDDH(string madh)
        {
            try
            {
                string sql = "delete CHITIET_PDH where MADH = '" + madh + "'";
                conn.ExcuteQuerySql(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
    }
}
